package com.demo.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.domain.ScientificDirection;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ScientificDirectionDao extends BaseMapper<ScientificDirection> {
//    查找所有科研方向，按教师名进行分组，各组内部按科研方向序号升序排序
    @Select("SELECT t1.* \n" +
            "FROM tb_scientific_direction t1 \n" +
            "INNER JOIN (\n" +
            "SELECT sd_name\n" +
            "FROM tb_scientific_direction \n" +
            "WHERE sd_is_delete = 0\n" +
            "GROUP BY sd_name) t2 \n" +
            "ON t1.sd_name = t2.sd_name\n" +
            "WHERE sd_is_delete = 0\n" +
            "ORDER BY t1.sd_name, t1.sd_direction")
    public List<ScientificDirection> selectAllDirection();

//    得到有科研方向的教师数量
    @Select("SELECT COUNT(DISTINCT sd_name) FROM `tb_scientific_direction` WHERE sd_is_delete = 0")
    public int selectTeaNum();

//    得到有科研方向的教师名字
    @Select("SELECT DISTINCT sd_name FROM `tb_scientific_direction` WHERE sd_is_delete = 0")
    public List<String> selectTeaName();

    @Select("SELECT DISTINCT(sd_name) FROM `tb_scientific_direction` WHERE sd_is_delete = 0 LIMIT #{number}")
    public List<String> selectLateTeacherName(int number);
}
